--网点签收规划签收汇总
insert overwrite table jms_dm.dm_terminal_sign_summary_plan_sign_new_dt partition(dt)
select order_source_name,--订单来源名称
       goods_type_code,--物品类型编码
       end_manage_region_code,--末端网点所属管理大区编码
       max(end_manage_region_name) as end_manage_region_name,--末端网点所属管理大区名称
       end_agent_code,--末端网点所属代理区编码
       max(end_agent_name) as end_agent_name,--末端网点所属代理区名称
       max(end_provider_id) as end_provider_id,--末端网点所属省份id
       max(end_provider_name) as end_provider_name,--末端网点所属省份名称
       max(end_city_id) as end_city_id,--末端网点所属城市id
       max(end_city_name) as end_city_name,--末端网点所属城市名称
       max(end_area_id) as end_area_id,--末端网点所属区县id
       max(end_area_name) as end_area_name,--末端网点所属区县名称
       end_center_code,--末端发件中心/集散编码
       max(end_center_name) as end_center_name,--末端发件中心/集散名称
       end_franchisee_code,--末端网点所属加盟商编码
       max(end_franchisee_name) as end_franchisee_name,--末端网点所属加盟商名称
       final_sign_network_code,--最终签收派件网点编码
       max(final_sign_network_name) as final_sign_network_name,--最终签收派件网点名称
       max(planned_delivery_time) as planned_delivery_time,--规划发件时间
       max(deadline_signing_time) as deadline_signing_time,--规划签收截止时间
       max(span_days) as span_days,--加时天数
       deliver_shift,--派件派仓班次
       final_sign_user_code,--最后签收派件员编码
       max(final_sign_user_name) as final_sign_user_name,--最后签收派件员名称
       sum(1) as need_sign_count,--应签收汇总
       sum(case when aging_sign_count_type = 11 then 1 else 0 end) as aging_sign_in_time_network,--时效签收准点-网点签收
       sum(case when aging_sign_count_type = 12 then 1 else 0 end) as aging_sign_in_time_terminal_pdd,--时效签收准点-终端入库(桃花岛认证)
       sum(case when aging_sign_count_type = 13 then 1 else 0 end) as aging_sign_in_time_terminal_other,--时效签收准点-终端入库(其他)
       sum(case when aging_sign_count_type = 14 then 1 else 0 end) as aging_sign_in_time_proxy,--时效签收准点-代理点收入
       sum(case when aging_sign_count_type = 15 then 1 else 0 end) as aging_sign_in_time_transfer,--时效签收准点-转邮
       sum(case when aging_sign_count_type in (11,12,13,14,15) then 1 else 0 end) as aging_sign_in_time_count,--时效签收准点-汇总
     --原始
       sum(case when aging_sign_count_type = 21 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_network,--时效签收延误-网点签收
       sum(case when aging_sign_count_type = 22 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_terminal_pdd,--时效签收延误-终端入库(桃花岛认证)
       sum(case when aging_sign_count_type = 23 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_terminal_other,--时效签收延误-终端入库(其他)
       sum(case when aging_sign_count_type = 24 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_proxy,--时效签收延误-代理点收入
       sum(case when aging_sign_count_type = 25 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_difficult,--时效签收延误-问题件
       sum(case when aging_sign_count_type = 26 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_other,--时效签收延误-其他
      sum(case when aging_sign_count_type = 27 and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_transfer,--时效签收延误-转邮
       sum(case when aging_sign_count_type in (21,22,23,24,27) and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_sign_count,--时效签收延误-已签收汇总
       sum(case when aging_sign_count_type in (25,26) and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_nosign_count,--时效签收延误-未签收汇总
       --新增1  10
       sum(case when aging_sign_count_type = 21 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_network,--时效签收延误-网点签收
       sum(case when aging_sign_count_type = 22 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_terminal_pdd,--时效签收延误-终端入库(桃花岛认证)
       sum(case when aging_sign_count_type = 23 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_terminal_other,--时效签收延误-终端入库(其他)
       sum(case when aging_sign_count_type = 24 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_proxy,--时效签收延误-代理点收入
       sum(case when aging_sign_count_type = 25 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_difficult,--时效签收延误-问题件
       sum(case when aging_sign_count_type = 26 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_other,--时效签收延误-其他
       sum(case when aging_sign_count_type = 27 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_transfer,--时效签收延误-转邮
       sum(case when aging_sign_count_type in (21,22,23,24,27) and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_sign_count,--时效签收延误-已签收汇总
       sum(case when aging_sign_count_type in (25,26) and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_nosign_count,--时效签收延误-未签收汇总
      --新增2
       sum(case when aging_sign_count_type = 21 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_network,--时效签收延误-网点签收
       sum(case when aging_sign_count_type = 22 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_terminal_pdd,--时效签收延误-终端入库(桃花岛认证)
       sum(case when aging_sign_count_type = 23 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_terminal_other,--时效签收延误-终端入库(其他)
       sum(case when aging_sign_count_type = 24 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_proxy,--时效签收延误-代理点收入
       sum(case when aging_sign_count_type = 25 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_difficult,--时效签收延误-问题件
       sum(case when aging_sign_count_type = 26 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_other,--时效签收延误-其他
       sum(case when aging_sign_count_type = 27 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_transfer,--时效签收延误-转邮
       sum(case when aging_sign_count_type in (21,22,23,24,27) and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_sign_count,--时效签收延误-已签收汇总
       sum(case when aging_sign_count_type in (25,26) and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_nosign_count,--时效签收延误-未签收汇总
    --结束
       sum(case when is_aging_sign_24 = 1 then 1 else 0 end) as aging_sign_24_count,--时效签收-24点前签收汇总
       sum(case when actual_sign_count_type = 11 then 1 else 0 end) as actual_sign_in_time_network,--实际签收准点-网点签收
       sum(case when actual_sign_count_type = 12 then 1 else 0 end) as actual_sign_in_time_terminal,--实际签收准点-驿站/快递柜
       sum(case when actual_sign_count_type = 13 then 1 else 0 end) as actual_sign_in_time_proxy,--实际签收准点-代理点收入
       sum(case when actual_sign_count_type in (11,12,13) then 1 else 0 end) as actual_sign_in_time_count,--实际签收准点-汇总
       sum(case when actual_sign_count_type = 21 then 1 else 0 end) as actual_sign_over_time_network,--实际签收延误-网点签收
       sum(case when actual_sign_count_type = 22 then 1 else 0 end) as actual_sign_over_time_terminal,--实际签收延误-驿站/快递柜
       sum(case when actual_sign_count_type = 23 then 1 else 0 end) as actual_sign_over_time_proxy,--实际签收延误-代理点收入
       sum(case when actual_sign_count_type = 24 then 1 else 0 end) as actual_sign_over_time_difficult,--实际签收延误-问题件
       sum(case when actual_sign_count_type = 25 then 1 else 0 end) as actual_sign_over_time_other,--实际签收延误-其他
       sum(case when actual_sign_count_type in (21,22,23) then 1 else 0 end) as actual_sign_over_time_sign_count,--实际签收延误-已签收汇总
       sum(case when actual_sign_count_type in (24,25) then 1 else 0 end) as actual_sign_over_time_nosign_count,--实际签收延误-未签收汇总
       sum(case when is_actual_sign_24 = 1 then 1 else 0 end) as actual_sign_24_count,--实际签收-24点前签收汇总
       final_plan_sign_date,--规划签收日期
       sum(case when is_aging_sign_00 = 1 then 1 else 0 end) as aging_sign_00_count,--时效签收-24点前签收汇总
       sum(case when is_aging_sign_22 = 1 then 1 else 0 end) as aging_sign_22_count,--时效签收-24点前签收汇总
       min(is_common_network) as is_common_network,--是否共配网点
       express_type_code,    --产品类型
       max(express_type_name) as express_type_name,
       --新增实际签收未维度  22前点汇总
       sum(case when actual_sign_count_type = 11 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_in_time_network,     --22点前实际签收准点-网点签收
       sum(case when actual_sign_count_type = 12 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_in_time_terminal,    --22点前实际签收准点-驿站/快递柜
       sum(case when actual_sign_count_type = 13 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_in_time_proxy,       --22点前实际签收准点-代理点收入
       sum(case when actual_sign_count_type in (11,12,13) and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)  as 22_actual_sign_in_time_count,       --22点前实际签收准点-汇总
       sum(case when actual_sign_count_type = 21 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_network,   --22点前实际签收延误-网点签收
       sum(case when actual_sign_count_type = 22 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_terminal,  --22点前实际签收延误-驿站/快递柜
       sum(case when actual_sign_count_type = 23 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_proxy,     --22点前实际签收延误-代理点收入
       sum(case when actual_sign_count_type = 24 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_difficult, --22点前实际签收延误-问题件
       sum(case when actual_sign_count_type = 25 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_other,     --22点前实际签收延误-其他
       sum(case when actual_sign_count_type in (21,22,23) and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_actual_sign_over_time_sign_count, --22点前实际签收延误-已签收汇总
       sum(case when actual_sign_count_type in (24,25) and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)     as 22_actual_sign_over_time_nosign_count, --22点前实际签收延误-未签收汇总
       --新增实际签收未维度  22-24点汇总
       sum(case when actual_sign_count_type = 11 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_in_time_network,     --22-24点实际签收准点-网点签收
       sum(case when actual_sign_count_type = 12 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_in_time_terminal,    --22-24点实际签收准点-驿站/快递柜
       sum(case when actual_sign_count_type = 13 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_in_time_proxy,       --22-24点实际签收准点-代理点收入
       sum(case when actual_sign_count_type in (11,12,13) and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end) as 24_actual_sign_in_time_count,       --22-24点实际签收准点-汇总
       sum(case when actual_sign_count_type = 21 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_network,   --22-24点实际签收延误-网点签收
       sum(case when actual_sign_count_type = 22 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_terminal,  --22-24点实际签收延误-驿站/快递柜
       sum(case when actual_sign_count_type = 23 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_proxy,     --22-24点实际签收延误-代理点收入
       sum(case when actual_sign_count_type = 24 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_difficult, --22-24点实际签收延误-问题件
       sum(case when actual_sign_count_type = 25 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_other,     --22-24点实际签收延误-其他
       sum(case when actual_sign_count_type in (21,22,23) and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end) as 24_actual_sign_over_time_sign_count,--22-24点实际签收延误-已签收汇总
      sum(case when actual_sign_count_type in (24,25) and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' = to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)     as 24_actual_sign_over_time_nosign_count,--22-24实际签收延误-未签收汇总
       --新增实际签收未维度  超24点汇总
       sum(case when actual_sign_count_type = 11  and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 00_actual_sign_in_time_network,     --超24点实际签收准点-网点签收
       sum(case when actual_sign_count_type = 12 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_in_time_terminal,    --超24点实际签收准点-驿站/快递柜
       sum(case when actual_sign_count_type = 13 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_in_time_proxy,       --超24点实际签收准点-代理点收入
       sum(case when actual_sign_count_type in (11,12,13) and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end) as 00_actual_sign_in_time_count,       --超24点实际签收准点-汇总
       sum(case when actual_sign_count_type = 21 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null))  then 1 else 0 end)         as 00_actual_sign_over_time_network,   --超24点实际签收延误-网点签收
       sum(case when actual_sign_count_type = 22 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_over_time_terminal,  --超24点实际签收延误-驿站/快递柜
       sum(case when actual_sign_count_type = 23 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_over_time_proxy,     --超24点实际签收延误-代理点收入
       sum(case when actual_sign_count_type = 24 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_over_time_difficult, --超24点实际签收延误-问题件
       sum(case when actual_sign_count_type = 25 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_over_time_other,     --超24点实际签收延误-其他
       sum(case when actual_sign_count_type in (21,22,23) and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end) as 00_actual_sign_over_time_sign_count,--超24点实际签收延误-已签收汇总
       sum(case when actual_sign_count_type in (24,25) and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)    as 00_actual_sign_over_time_nosign_count,--超24点实际签收延误-未签收汇总
       sum(if( actual_sign_time < concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'22:00:00'),1,0)) as actual_sign_22_count,   -- 22点签收量
       sum(if( actual_sign_time < concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'23:59:59'),1,0)) as actual_sign_00_count,   --24点前前签收量
       third_code,  --三段码code
       max(third_name) as third_name --三段码名字
       ,max(end_brand_code) as end_brand_code
       ,max(end_brand_name) as end_brand_name
       ,sum(if(terminal_type='驿站',1,0)) as yizhan_sum   --驿站
      ,sum(if(terminal_type='柜机',1,0)) as guiji_sum   --柜机
      ,sum(if(aging_sign_scan_type ='快件签收',1,0)) as shsm_sum   --送货上门
      ,sum(if(deliver_township_name is not null,1,0)) as township_sum   --乡镇件
    ,max(is_shaidan) as is_shaidan                 --是否筛单网点
    ,max(is_delay) as is_delay                        -- 是否顺延网点
    ,max(is_new_open) as is_new_open                     -- 是否新开网点
    ,max(is_special_report) as is_special_report               -- 是否特殊上报网点
    ,sum(if(actual_sign_time is null,1,0)) as un_active_sign_sum   --未实际签收量
    ,sum(if(collect_in_time is not null and collect_out_time is null ,1,0))    as in_no_out_collect  --有入库未出库总量
       ,final_plan_sign_date as dt
 from (
                select t.*,
                       first_value(is_common) over (partition by final_plan_sign_date,final_sign_network_code order by is_common asc nulls last) is_common_network
                from jms_dm.dm_terminal_sign_detail_new_dt t
                where dt between date_format(date_add('{{ execution_date | cst_ds }}', -29), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
                  and final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -14), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
          ) t
group by final_plan_sign_date,
         order_source_name,
         goods_type_code,
         end_manage_region_code,
         end_agent_code,
         end_center_code,
         end_franchisee_code,
         final_sign_network_code,
         deliver_shift,
         final_sign_user_code,
         express_type_code,
         third_code
distribute by dt,pmod(hash(rand()),10)
 ;